#include "Actions.h"

extern vector<string> AllowedVendors;

void PerformAction ( int ChosenAction, DBContext& Db, vector<string>& AllowedVendors, vector<string>& CardOwners  )
	{
	if ( ChosenAction == 1 )
		{
		NewVendor( Db, AllowedVendors );
		}
	else if ( ChosenAction == 2)
		{
		cout << "Format: Vendor_Name(*), Store_Type(*), Address(*), Authorized_Since(YYYY/MM/DD)" << endl;
		cout << "(*) == Maximum 50 characters" << endl << endl;
		NewVendorFromFile( Db, AllowedVendors );
		}
	else if ( ChosenAction == 3 )
		{
		PrintVendorList( Db );
		}
	else if ( ChosenAction == 4 )
		{
		NewCardOwner( Db, CardOwners );
		}
	else if ( ChosenAction == 5 )
		{
		cout << "Format: Name(*), Card_Number(*), Member_Since(YYYY/MM/DD), Card_Balance" << endl;
		cout << "(*) == Maximum 50 characters" << endl << endl;
		NewCardOwnerFromFile( Db, CardOwners );
		}
	else if ( ChosenAction == 6 )
		{
		PrintCardOwnerList( Db );
		}
	else if ( ChosenAction == 7 )
		{
		NewTransaction( Db, AllowedVendors, CardOwners );
		}
	else if ( ChosenAction == 8)
		{
		cout << "Date format: YYYY/MM/DD" << endl << endl;
		NewTransactionFromFile( Db, AllowedVendors, CardOwners );
		}
	else if ( ChosenAction == 9 )
		{
		PrintMonthlyStatement( Db, CardOwners );
		}
	else if ( ChosenAction == 10 )
		{
		PrintVendorSummary( Db, AllowedVendors );
		}
	else if ( ChosenAction == 11 )
		{
		QueryTransactionTable( Db, AllowedVendors, CardOwners);
		}	
	if ( ChosenAction == 12 )
		{
		CreateTable( Db );
		}
	else if ( ChosenAction == 13 )
		{
		DeleteTable( Db );
		}
	
	/* INSERT TUPLE is carried out in NewVendor(), NewCardOwner(), etc */

	else if ( ChosenAction == 14 )
		{
		UpdateTuple( Db );
		}
	else if (ChosenAction == 15 )
		{
		AggregateQuery( Db );
		}
	else if ( ChosenAction == 0 )
		{
		return;
		}
	}

/***********************************************OPTION ONE**********************************************
********************************************************************************************************/

void NewVendor( DBContext& Db, vector<string>& AllowedVendors )	// Become an allowed vendor
	{
	cout << "Please fill in the following blanks." << endl;
	
	
	cout << "Vendor Name: ";	// Read in the Vendor Name
	string VendorName;
	ReadSeveralWords( VendorName );
	AllowedVendors.push_back( VendorName );
	
	
	cout << "Store Type: ";		// Read in the store type
	string StoreType;
	ReadSeveralWords( StoreType );
	
	
	cout << "Vendor Address: ";	// Read in the Vendor Address
	string Address;
	ReadSeveralWords( Address );
	
	
	cout << "Today's Date (YYYY/MM/DD): ";	// Read in Today's Date
	string AuthorizedSince;
	ReadSingleWord( AuthorizedSince );
	while ( !ValidDate( AuthorizedSince ) )	// If the Date was invalid, try again
		{
		cout << "Today's Date (YYYY/MM/DD): ";	// Read in Today's Date
		ReadSingleWord( AuthorizedSince );
		}
	cout << endl;
	
	
	// Initialize the command string
	string command = "INSERT INTO AuthorizedVendors VALUES (" + VendorName + ", " + StoreType + ", " + Address + ", " + AuthorizedSince + ")";
	// Insert tuple
	Db.ExecuteNonQuery(command);
	}
	

bool AllowedVendor( string Name, vector<string>& AllowedVendors )
	{
	for (int i = 0; i < AllowedVendors.size(); ++i )
		{
		if ( AllowedVendors[i] == Name )
			{
			return true;
			}
		}
	return false;
	}


void IgnoreLeftOverInput()
	{
	char ch = cin.peek();
	if ( (ch == '\n') || (ch >= 32) )	// New-line, space, alphanumeric or special character
		{
		cin.ignore(std::numeric_limits<int>::max(),'\n');	// Ignore leftover input
		}	
	}

void ReadSingleWord( string& word )	// Reads in one word, ignores the rest
	{
	IgnoreLeftOverInput();     // Keep previous operations' leftover input from interfering with this operation
	cin >> word;	// Read one word
	cout << endl;
	}

void ReadSeveralWords( string& word )	// Reads in several words, fills in the spaces
	{
	IgnoreLeftOverInput();     // Keep previous operations' leftover input from interfering with this operation
	word = "";
	string buf;
	vector<string> IndividWords;
	cin >> buf;
	IndividWords.push_back( buf );	// Push back first word
	while ( cin.peek() != '\n' )	// \n indicates we've reached the last word
		{
		cin >> buf;
		IndividWords.push_back( buf );
		}


	for ( int i = 0; i < IndividWords.size(); ++i )
		{
		word += IndividWords[i];
		if ( i != IndividWords.size() - 1 )
			{
			word += " ";
			}
		}


	FillInSpaces( word );
	cout << endl;
	}


void FillInSpaces( string& Word )	// Replace all 'space' characters with '_', i.e. Burger King --> Burger_King
	{
	for ( int i = 0; i < Word.size(); ++i )	// Traverse through each character in Word
		{
		if ( Word[i] == ' ' )	// If the character is white-space
			{
			Word[i] = '_';	// Replace with '_'
			}
		}
	}
	
	
bool ValidDate( string &Date )	
	{ // Date must be in YYYY/MM/DD format
	if ( Date.size() != 10 )
		{
		cout << "Invalid format: Missing characters" << endl << endl;
		return false;
		}
	
	
	// Check special characters first
	char ch1, ch2;
	ch1 = Date[4];
	ch2 = Date[7];
	if ( (ch1 != '/') || (ch2 != '/') )
		{
		cout << "Invalid Date format. Expected '/' character." << endl << endl;
		return false;
		}
	
	
	// Now check the int values
	string Year = "    ";	// 4 spaces
	for (int i = 0; i < 4; ++i)
		{
		Year[i] = Date[i];
		}	
	string Month = "  ";	// 2 spaces
	Month[0] = Date[5];
	Month[1] = Date[6];
	string Day = "  ";	// 2 spaces
	Day[0] = Date[8];
	Day[1] = Date[9];
	if (!ValidDateValues(Day, Month, Year))	// if the year, month and/or days values are invalid
		{
		return false;
		}
	
	
	// Valid date
	
	
	// In case they entered extraneous input
	Date = Year + "/" + Month + "/" + Day;
	return true;
	}
	
	
bool ValidDateValues( string Day, string Month, string Year )	// Checks if year, month and day values are valid
	{
	int Year_int;
	if ( !toInt( Year, Year_int ) )	// if the string-to-int conversion fails
		{
		cout << "Invalid: Year value was not an integer" << endl << endl;
		return false;
		}
	if (!ValidYear(Year_int))	// Invalid year
		{
		return false;
		}
		
	
	int Month_int;
	if ( !toInt( Month, Month_int ) )	// if the string-to-int conversion fails
		{
		cout << "Invalid: Month value was not an integer" << endl << endl;
		return false;
		}
	if (!ValidMonth(Month_int))	// Invalid month
		{
		return false;
		}

	
	int Day_int;
	if ( !toInt( Day, Day_int) )	// If the string-to-int conversion fails
		{
		cout << "Invalid: Day value was not an integer" << endl << endl;
		return false;
		}
	if ((Day_int < 1) || (Day_int > 31))
		{
		cout << "Invalid: Day value was not within range [1:31]" << endl << endl;
		return false;
		}
	
	
	// Special cases
	// If it is February
	if (Month_int == 2)		
		{
		if (Day_int > 29)
			{
			cout << "Invalid Day for February." << endl << endl;
			return false;
			}
		if ( (Day_int == 29) && ((Year_int%4) != 0))	// 29 days in a non leap-year
			{
			cout << "Invalid: 29 days in a non-leap year." << endl << endl;
			return false;
			}
		}

	
	// If it is a 30-day month	
	if (( (Month_int == 4) || (Month_int == 6) || (Month_int == 9) || (Month_int == 11) ) &&	
			(Day_int > 30) )
		{
		cout << "Invalid. This month has only 30 days." << endl << endl;
		return false;
		}
		
		
	return true;
		
	}
	
	
bool ValidYear( int Year )	// Checks if a valid year [0:9999] was entered
	{
	if ( (Year < 0) || (Year > 9999) )	// Invalid year
		{
		cout << "Invalid: Year value was not within range [0000:9999]" << endl << endl;
		return false;
		}
		
	
	return true;
	}
	
	
bool ValidMonth( int month )	// Checks if a valid month [1:12] was entered
	{
	if ( (month < 1) || (month > 12) )	// Invalid month
		{
		cout << "Invalid: Month value was not within range [1:12]." << endl << endl;
		return false;
		}
	
	
	return true;
	}


/***********************************************OPTION TWO**********************************************
********************************************************************************************************/


void NewVendorFromFile( DBContext& Db, vector<string>& AllowedVendors )	// Insert a new vendor from file
	{
	cout << "File name: ";
	string filename;
	ReadSingleWord( filename );
	
	
	// Open file
	ifstream is;
	if ( ! ( OpenFile( is, filename ) ) )	// If the file is not successfully opened
		{
		return;
		}
			
	
	// Insert the vendors
	while ( !( is.eof() ) )
		{
		// Obtain the values (Assume valid formatting)
		string VendorName;	// The name may be more than one word, so we use get() function to read whitespace
		ReadWordFromFile( is, VendorName );
		AllowedVendors.push_back( VendorName );

		
		string StoreType;
		ReadWordFromFile( is, StoreType );		
		
		
		string Address;
		ReadWordFromFile( is, Address );
		
		
		string AuthorizedSince;	// Date is only one word, and contains no comma. Hence, no need to call ReadWordFromFile()
		is >> AuthorizedSince;
		
		
		// Initialize the command string
		string command = "INSERT INTO AuthorizedVendors VALUES (" + VendorName + ", " + StoreType + ", " + Address + ", " + AuthorizedSince + ")";
		
		
		// Insert tuple
		Db.ExecuteNonQuery(command);
		}
	
	}


bool OpenFile( ifstream& is, string filename)
	{
	is.open( filename.c_str() );
	
	
	if ( !is )
		{
		cout << "Could not open " << filename << endl << endl;
		return false;
		}
	
	
	return true;
	}

void ReadWordFromFile( ifstream& is, string& Word )	// Reads one or more words from file
	{
		char ch = ' ';
		is >> ch;
		while ( ch != ',' )
			{
			Word += ch;
			is.get(ch);
			}
		FillInSpaces( Word );	// Fill in the spaces
	}

	
/***********************************************OPTION THREE**********************************************
********************************************************************************************************/

void PrintVendorList( DBContext& Db ) 	// See list of allowed vendors
	{
	PrintTable( Db, "SELECT * FROM AuthorizedVendors" );
	}

	
void PrintTable( DBContext& Db, string Query )
	{
	Entity vendorResult;
	Db.ExecuteReader( Query, &vendorResult);

	//print entity info
	cout << Query << ":\n";
	for each(ColumnDefinition coll in vendorResult.Columns)
		{
		cout<<coll.ToString()<<"\t";
		}
	cout<<endl;

	//print rows
	int count=1;
	for each(void* row in vendorResult.Rows)
		{
		cout<<count<<": ";
		void* readPtr = row;
		for each(ColumnDefinition col in vendorResult.Columns){
			switch(col.GetType()) 
				{
				case DATE:				
					cout<<((Date*)readPtr)->ToString()<<"\t";
					readPtr =  ((Date*)(readPtr))+1;
					break;
				case FLOAT:
					cout<< ((Float*)readPtr)->ToString()<<"\t";
					readPtr =  ((Float*)(readPtr))+1;
					break;
				case INT:
					cout<<((Int*)readPtr)->ToString()<<"\t";
					readPtr =  ((Int*)(readPtr))+1;
					break;
				case TIME:
					cout<<((Time*)readPtr)->ToString()<<"\t";
					readPtr =  ((Time*)(readPtr))+1;
					break;
				case VARCHAR:
					cout<<((String*)readPtr)->ToString()<<"\t";
					readPtr =  ((String*)(readPtr))+1;
					break;
				default:
					cout<<"Error"<<endl;
					return;
					break;
				}
			}
		cout<<endl;
		}
	}

/***********************************************OPTION FOUR**********************************************
********************************************************************************************************/

void NewCardOwner( DBContext& Db, vector<string>& CardOwners ) 	// Become a card owner
	{ 
	cout << "Please fill in the following blanks." << endl;
	
	
	cout << "Owner Name: ";	// Read in the Owner Name
	string OwnerName;
	ReadSeveralWords( OwnerName );
	
	
	cout << "Card Number (16 digits): ";		// Read in the Card Number
	string CardNumber;
	ReadSixteenDigits( CardNumber );
	while ( CardNumberExists( CardNumber, CardOwners ) )	// If the card number is already taken
		{
		cout << "Card number [" << CardNumber << " already taken. Please try again." << endl << endl;
		ReadSixteenDigits( CardNumber );
		}
	CardOwners.push_back( CardNumber);

	
	cout << "Today's Date (YYYY/MM/DD): ";	// Read in Today's Date
	string Date;
	ReadSingleWord( Date );
	while ( !ValidDate( Date ) )	// If the Date was invalid, try again
		{
		cout << "Today's Date (YYYY/MM/DD): ";
		ReadSingleWord( Date );
		}
	
	
	cout << "Initial Balance: ";	// Read in the Initial Balance
	string Balance;
	ReadDouble( Balance, "Initial Balance: " );
	ShaveOffDigits( Balance );	// We only want two digits after the decimal point
	
	
	// Initialize the command string
	string command = "INSERT INTO CardOwners VALUES (" + OwnerName + ", " + CardNumber + ", " + Date + ", " + Balance + ")";
	// Insert tuple
	Db.ExecuteNonQuery(command);
	}


bool CardNumberExists( string CardNumber, vector<string>& CardOwners )
	{
	for ( int i = 0; i < CardOwners.size(); ++i )
		{
		if ( CardOwners[i] == CardNumber )
			{
			return true;
			}
		}

	return false;
	}
	
	
void ReadSixteenDigits( string& Number )	// Reads in Sixteen digits; makes sure the input is valid
	{
	IgnoreLeftOverInput();	// Keep previous input operations from interfering with this one
	Number = "";
	char ch;
	// Read in the Sixteen digits
	for ( int i = 0; i < 16; ++i )
		{
		cin.get(ch);
		// If user entered a non-digit, print error message and start over
		if ( !isdigit( ch ) )	
			{
			cout << "Invalid: A non-digit was entered" << endl << endl;
			
			// Start over
			Number = "";
			IgnoreLeftOverInput();     // Keep previous leftover input from interfering with the next input
			i = -1;
			cout << "Card Number (16 digits): ";
			}
		else
			{
			Number += ch;
			}
		}


	cout << endl;
	}
	
	
void ReadDouble( string& Double, string message )	// Reads in a double; makes sure the input is valid
	{
	Beginning:
	IgnoreLeftOverInput();     // Keep previous operations' leftover input from interfering with this operation
	cin >> Double;


	bool PeriodFoundAlready = false;
	for ( int i = 0; i < Double.size(); ++i )
		{	
		// Each character should either be a digit or '.'
		if ( (!isdigit( Double[i] ) && Double[i] != '.' ) || ( Double[i] == '.' && PeriodFoundAlready ) )
			{	
			// If not, print message and start over
			cout << message;
			goto Beginning;	
			}
		else if ( Double[i] == '.' && !PeriodFoundAlready )
			{
			PeriodFoundAlready = true;
			}
		}
	
	cout << endl;
	}

void ShaveOffDigits( string& Balance )
	{
	// Too many digits after the decimal point?
	int i;
	for ( i = 0; i < Balance.size(); ++i )	// Find the decimal point
		{
		if ( Balance[i] == '.' )
			{
			for ( int j = Balance.size() - 1; j > i + 2 ; --j )	// 'Shave' off extra digits (if any)
				{
				Balance.erase( Balance.begin() + j );
				}

			break;
			}
		}
	}


/***********************************************OPTION SIX**********************************************
********************************************************************************************************/


void NewCardOwnerFromFile( DBContext& Db, vector<string>& CardOwners)
	{
	cout << "File name: ";
	string filename;
	ReadSingleWord( filename );
	
	
	// Open file
	ifstream is;
	if ( !OpenFile( is, filename ) )	// If we could not open the file
		{
		return;
		}
		
	
	// Insert the new card owner
	while ( !( is.eof() ) )
		{
		// Obtain the values (Assume valid formatting)
		string OwnerName;	// The name may be more than one word, so we use get() function to read whitespace
		ReadWordFromFile( is, OwnerName );


		string CardNumber;
		ReadWordFromFile( is, CardNumber );
		
		
		string MemberSince;	// YYYY/MM/DD
		ReadWordFromFile( is, MemberSince );
		
	
		string CardBalance;
		is >> CardBalance;
		ShaveOffDigits( CardBalance );	// Remove extra digits after the decimal point (if necessary)
		
		
		// Check if the card number is already taken
		if ( CardNumberExists( CardNumber, CardOwners ) )
			{
			cout << "Card Number [" << CardNumber << "] already taken. Could not insert card owner." << endl << endl;
			return;
			}
		

		// Initialize the command string
		string command = "INSERT INTO CardOwners VALUES (" + OwnerName + ", " + CardNumber + ", " + MemberSince + ", " + CardBalance + ")";
		// Insert tuple
		Db.ExecuteNonQuery(command);
		}
	
	}



/***********************************************OPTION SIX**********************************************
********************************************************************************************************/

void PrintCardOwnerList( DBContext& Db )	// See list of card owners
	{
	PrintTable( Db, "SELECT * FROM CardOwners" );
	}

/***********************************************OPTION SEVEN**********************************************
********************************************************************************************************/

void NewTransaction( DBContext& Db, vector<string>& AllowedVendors, vector<string>& CardOwners ) 	// Store a new transaction
	{
	cout << "Please fill in the following blanks." << endl;
	
	
	cout << "Amount Transacted: ";	// Read in the amount transacted
	string Amount;
	ReadDouble( Amount, "Amount Transacted: " );
	ShaveOffDigits( Amount );	// Remove extra digits after the decimal point (if neccessary)

	
	cout << "Bought from (Vendor Name): ";		// Read in the vendor the transaction occured from
	string VendorName;
	ReadSeveralWords( VendorName );
	if ( !AllowedVendor( VendorName, AllowedVendors ) )
		{
		cout << VendorName << " not an allowed vendor. Transaction not stored." << endl << endl;
		return;
		}

	cout << "Card Number (16 digits): ";		// Read in the Card Number
	string CardNumber;
	ReadSixteenDigits( CardNumber ); 
	if ( !CardNumberExists( CardNumber, CardOwners ) )
		{
		cout << "Card Number[" << CardNumber << "] does not exist. Transaction not stored." << endl << endl;
		return;
		}
	

	cout << "Purchase Type: ";	// Read in the Purchase Type
	string PurchaseType;
	ReadSeveralWords( PurchaseType );

	
	cout << "Transaction Date (YYYY/MM/DD): ";	// Read in the date of the transaction
	string Date;
	ReadSingleWord( Date );
	while ( !ValidDate( Date ) )	// If the Date is invalid, try again
		{
		cout << "Transaction Date (YYYY/MM/DD): ";
		ReadSingleWord( Date );
		}
	
	
	cout << "Transaction Time (HH:MM:SS): ";	// Read in the time of the transaction
	string Time;
	ReadSingleWord( Time );
	while ( !ValidTime( Time ) )	// If the Time is invalid, try again
		{
		cout << "Transaction Time (YYYY/MM/DD): ";
		ReadSingleWord( Time );
		}
	
	
	cout << "Transaction ID: ";	// Read in the Transaction ID
	string TransactionID;
	ReadSingleWord( TransactionID );

	
	// Initialize the command string
	string command = "INSERT INTO TransactionsPerformed VALUES (" + Amount + ", " + VendorName + ", " + CardNumber + ", " + PurchaseType + ", " + Date + ", " + Time + ", " + TransactionID + ")";
	// Insert tuple
	Db.ExecuteNonQuery(command);	
	}
	
	
bool ValidTime( string Time )	// Checks for valid time format and time values
	{
	// Check formatting
	if ( Time.size() != 8 )
		{
		cout << "Invalid format: Missing characters" << endl << endl;
		return false;
		}
	if ( ( Time[2] != ':' ) || ( Time[5] != ':' ) )
		{
		cout << "Invalid: Expected ':' character" << endl << endl;
		return false;
		}
		

	string hour = "  ";		// 2 spaces
	hour[0] = Time[0];
	hour[1] = Time[1];
	string minute = "  ";
	minute[0] = Time[3];
	minute[1] = Time[4];
	string second = "  ";
	second[0] = Time[6];
	second[1] = Time[7];
	int int_hour, int_minute, int_second;
	
	
	// Check if the values for hour, minute and second were all valid integers
	if ( !( toInt( hour, int_hour ) ) )
		{	// If the conversion was unsuccessful
		cout << "Invalid character for 'hours' entered" << endl << endl;
		return false;
		}
	if ( !( toInt( minute, int_minute ) ) )
		{	// If the conversion was unsuccessful
		cout << "Invalid character for 'minutes' entered" << endl << endl;
		return false;
		}
	if ( !( toInt( second, int_second ) ) )
		{	// If the conversion was unsuccessful
		cout << "Invalid character for 'seconds' entered" << endl << endl;
		return false;
		}
		
	
	// All values must be within range
	
	
	if ( ( int_hour < 0 ) || ( int_hour > 23 ) )
		{
		cout << "'hours' is out of range [0:23]" << endl << endl;
		return false;
		}
	if ( ( int_minute < 0 ) || ( int_minute > 59 ) )
		{
		cout << "'minutes' is out of range [0:59]" << endl << endl;
		return false;
		}
	if ( ( int_second < 0 ) || ( int_second > 59 ) )
		{
		cout << "'seconds' is out of range [0:59]" << endl << endl;
		return false;
		}
		
	
	// We have a valid time
	return true;
	}
	

bool toInt( string s, int& i )	// Convert string s to an int
	{
	stringstream ss;
	ss << s;
	if ( !(ss >> i) )	// If conversion fails
		{
		return false;
		}
		

	return true;
	}
	


/***********************************************OPTION EIGHT**********************************************
********************************************************************************************************/


void NewTransactionFromFile( DBContext& Db, vector<string>& AllowedVendors, vector<string>& CardOwners ) 
	{ 
	cout << "File name: ";
	string filename;
	ReadSingleWord( filename );


	// Open file
	ifstream is;
	if ( !OpenFile( is, filename ) )	// If we could not open the file
		{
		return;
		}

		
	// Insert the transactions
	while ( !( is.eof() ) )
		{
		// Obtain the values (Assume valid formatting)
		string Date;
		ReadWordFromFile( is, Date );
	

		string Time;
		ReadWordFromFile( is, Time );
		

		string CardNumber;
		ReadWordFromFile( is, CardNumber );


		string VendorName;	
		ReadWordFromFile( is, VendorName );
		

		string Amount;
		ReadWordFromFile( is, Amount );
		

		string PurchaseType;
		ReadWordFromFile( is, PurchaseType );
		

		string TransactionID;
		is >> TransactionID;
		
		// Insert tuple
		if ( !AllowedVendor( VendorName, AllowedVendors ) )
			{
			cout << VendorName << " not an allowed Vendor. Transaction not stored." << endl << endl;
			}
		else if ( !CardNumberExists( CardNumber, CardOwners ) )
			{
			cout << "Card Number[" << CardNumber << "] does not exist. Transaction not stored." << endl << endl;
			}
		else
			{
			string command = "INSERT INTO TransactionsPerformed VALUES (" + Amount + ", " + VendorName + ", " + CardNumber + ", " + PurchaseType + ", " + Date + ", " + Time + ", " + TransactionID + ")";
			Db.ExecuteNonQuery(command);	
			}
		}
	}


void RemoveComma( string& s )
	{
	const int CommaIndex = s.size() - 1;
	if ( s[CommaIndex] == ',' )
		{	// Remove the comma
		s.erase( s.begin() + CommaIndex );
		}
	}


/***********************************************OPTION NINE**********************************************
********************************************************************************************************/


void PrintMonthlyStatement( DBContext& Db, vector<string>& CardOwners ) 	// Print a monthly statement
	{
	cout << "Card Number (16 digits): ";		// Read in the Card Number
	string CardNumber;
	ReadSixteenDigits( CardNumber );
	if ( !CardNumberExists( CardNumber, CardOwners ) )
		{
		cout << "Cannot generate statement: Card Number[" << CardNumber << "] does not exist." << endl << endl;
		return;                                        
		}


	// Get the Date range
	cout << "Start Date (YYYY/MM/DD): ";
	string StartDate;
	ReadSingleWord( StartDate );
	while ( !ValidDate( StartDate ) )                                                                                        
		{
		cout << "Start Date (YYYY/MM/DD): ";
		ReadSingleWord( StartDate );
		}
	

	cout << "End Date (YYYY/MM/DD): ";
	string EndDate;
	ReadSingleWord( EndDate );
	while ( !ValidDate( EndDate ) )
		{
		cout << "End Date (YYYY/MM/DD): ";
		ReadSingleWord( EndDate );
		}


	string command = "SELECT * FROM TransactionsPerformed WHERE ( CardNumber = '" + CardNumber +"') AND ( Date > " + StartDate + " ) AND ( Date < " + EndDate + " )";
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	PrintTable( Db, command );
	}



/***********************************************OPTION TEN**********************************************
********************************************************************************************************/


void PrintVendorSummary( DBContext& Db, vector<string>& AllowedVendors )	// Print a vendor transaction summary
	{
	cout << "Vendor Name: ";
	string VendorName;
	ReadSeveralWords( VendorName );
	
	
	if ( !AllowedVendor( VendorName, AllowedVendors ) )
		{
		cout << "Cannot generate statement. " << VendorName << " is not an allowed vendor." << endl << endl;
		return;
		}


	string command = "SELECT * FROM TransactionsPerformed WHERE ( VendorName = '" + VendorName + "' )";
	PrintTable( Db, command );
	}


/***********************************************OPTION ELEVEN**********************************************
********************************************************************************************************/


void QueryTransactionTable( DBContext& Db, vector<string>& AllowedVendors, vector<string>& CardOwners )	// Query the "Transactions Performed" table
	{
	cout << "Which type of query:" << endl << endl;
	cout << "1 \tQuery an individual transaction" << endl;
	cout << "2 \tQuery transactions within a specific DATE range" << endl;
	cout << "3 \tQuery transactions within a specific TIME range" << endl << endl; 
	cout << "Query: ";

	char query;
	int ChosenQuery;
	IgnoreLeftOverInput();     // Keep previous operations' leftover input from interfering with this operation
	
	ChooseQuery:
	while ( true )	
		{
		cin >> query;
		cout << endl;
		// Make sure the user entered a digit
		if ( isdigit( query ) )	// If the user did, read it into the int object
			{
			cin.putback( query );
			cin >> ChosenQuery;
			break;
			}
		else	// Otherwise, try again.
			{
			cout << "Please enter a digit" << endl << "Query: ";
			IgnoreLeftOverInput();
			}
		}
		
	// Is the digit within range?
	if ( (ChosenQuery < 1) || (ChosenQuery > 3) )	// If not, read in a new integer
		{
		cout << "Please enter a digit within [1:3]" << endl << "Query: ";
		goto ChooseQuery;
		}
	else	// If so, we are ready to perform the query
		{
		PerformQuery( Db, ChosenQuery, AllowedVendors, CardOwners );
		}
	}


void PerformQuery( DBContext& Db, int ChosenQuery, vector<string>& AllowedVendors, vector<string>& CardOwners )	// Performs the chosen query for option eleven
{
	if ( ChosenQuery == 1)
	{
		QueryTransaction( Db, AllowedVendors );
	}
	else if (ChosenQuery == 2)
	{
		
		QueryTransaction_Date(Db);	/*	Though the request was not specifically for a monthly statement, 
												this function provides the exact functionality we need */
	}
	else if (ChosenQuery == 3)
	{
		QueryTransaction_Time(Db);
	}
}


void QueryTransaction( DBContext& Db,  vector<string>& AllowedVendors )	// Queries for an individual transaction
	{
	cout << "Transaction ID: ";
	string TransactionID;
	ReadSingleWord( TransactionID );
	
	
	cout << "Vendor Name: ";
	string VendorName;
	ReadSeveralWords( VendorName );
	if ( !AllowedVendor( VendorName, AllowedVendors ) )
		{
		cout << "Cannot perform query. " << VendorName << " is not an allowed vendor." << endl << endl;
		return;
		}	


	string command = "SELECT * FROM TransactionsPerformed WHERE ( TransactionID = '" + TransactionID + "' ) AND ( VendorName = '" + VendorName + "' )";
	PrintTable( Db, command );
	}


void QueryTransaction_Date( DBContext& Db )	// Queries all transactions within a specific date range
	{
	// Get the Date range
	cout << "Start Date (YYYY/MM/DD): ";
	string StartDate;
	ReadSingleWord( StartDate );
	while ( !ValidDate( StartDate ) )
		{
		cout << "Start Date (YYYY/MM/DD): ";
		ReadSingleWord( StartDate );
		}
	

	cout << "End Date (YYYY/MM/DD): ";
	string EndDate;
	ReadSingleWord( EndDate );
	while ( !ValidDate( EndDate ) )
		{
		cout << "End Date (YYYY/MM/DD): ";
		ReadSingleWord( EndDate );
		}


	string command = "SELECT * FROM TransactionsPerformed WHERE ( Date > " + StartDate + " ) AND ( Date < " + EndDate + " )";
	PrintTable( Db, command );
	}


void QueryTransaction_Time( DBContext& Db )	// Queries all transactions within a time range
	{
	cout << "Start Time (HH:MM:SS) : ";
	string StartTime;
	ReadSingleWord( StartTime );
	while ( !ValidTime( StartTime ) )	// Make sure the time is vaild
		{
		cout << "Start Time (HH:MM:SS) : ";
		ReadSingleWord( StartTime );
		}
	

	cout << "End Time (HH:MM:SS) : ";
	string EndTime;
	ReadSingleWord( EndTime );
	while ( !ValidTime( EndTime ) )	// Make sure the time is vaild
		{
		cout << "End Time (HH:MM:SS) : ";
		ReadSingleWord( EndTime );
		}
	

	string command = "SELECT * FROM TransactionsPerformed WHERE ( Time > " + StartTime + " ) AND ( Time < " + EndTime + " )";
	PrintTable( Db, command );
	}

	
/***********************************************OPTION TWELVE**********************************************
**********************************************************************************************************/


void CreateTable( DBContext& Db )
	{
	// Read in the table's name
	cout << "Table Name: ";
	string TableName;
	ReadSeveralWords( TableName );

	cout << "Number of columns: ";
	int no_of_columns;
	cin >> no_of_columns;

	//Get column information
	vector<string> column_names;
	vector<string> column_types;
	string column_name;
	string column_type;
	for ( int i = 0; i < no_of_columns; ++i )
		{
		cout << "Column Name: ";
		ReadSeveralWords( column_name );
		column_names.push_back( column_name );
		cout << endl << "Column Type: ";
		ReadSingleWord( column_type );
		column_types.push_back( column_type );
		cout << endl;
		}

	/* Create 'Command' string here */
	string command = "CREATE TABLE " + TableName + "(";
	for ( int i = 0; i < column_names.size(); ++i )
		{
		command += column_names[i] + " " + column_types[i];
		
		if ( i == column_names.size() - 1 )
			{
			command += ')';
			}
		else
			{
			command += ',';
			}
		}
	Db.ExecuteNonQuery( command );

	}


/***********************************************OPTION THIRTEEN**********************************************
************************************************************************************************************/


void DeleteTable( DBContext& Db )
	{
	// Read in the table's name
	cout << "Table Name: ";
	string TableName;
	ReadSeveralWords( TableName );

	/* Create 'Delete Command' here */
	string command = "DROP TABLE " + TableName;
	Db.ExecuteNonQuery( command );
	}


/***********************************************OPTION FOURTEEN**********************************************
************************************************************************************************************/


void UpdateTuple( DBContext& Db )
	{
	// Read in the table's name
	cout << "Table Name: ";
	string TableName;
	ReadSeveralWords( TableName );

	// Read in he SET assignmens
	cout << "Enter attribute assignments one at a time i.e. \"Name = Bank Of America\"" << endl;
	cout << "Enter \"Done\" when finished." << endl;
	vector<string> attributes;
	string attribute = "";
	cout << "Attribute: ";
	ReadSeveralWords( attribute );
	while ( attribute != "DONE" )
	{
		attributes.push_back( attribute );
		cout << "Attribute: ";
		ReadSeveralWords( attribute );
	}

	cout << "Enter the WHERE condition: ";
	string WHERE;
	ReadSeveralWords( WHERE );

	/* Create UPDATE command here */
	string command = "UPDATE " + TableName + " SET ";
	for ( int i = 0; i < attributes.size(); ++i )
		{
		command += attributes[i];
		if ( i != attributes.size() - 1 )
			{
			command += ', ';
			}
		else
			{
			command += ' ';
			}
		}
		
	command += WHERE;
		
	Db.ExecuteNonQuery( command );
	}


/***********************************************OPTION FIFTEEN**********************************************
************************************************************************************************************/



void AggregateQuery( DBContext& Db )
	{
	cout << "Aggregate type ( SUM, COUNT, MIN, MAX ): ";
	string aggr_type;
	ReadSingleWord( aggr_type );

	cout << "Columns (may include 'AS'): ";
	string ColumnNames;
	ReadSeveralWords( ColumnNames );

	cout << "TableName: ";
	string TableName;
	ReadSeveralWords( TableName );

	cout << "'WHERE' condition: ";
	string WHERE;
	ReadSeveralWords( WHERE );

	/* Create command here */
	string command = "SELECT " + aggr_type + "(" + ColumnNames + ") FROM " + TableName + " WHERE (" + WHERE + ")";

	Entity result;
	Db.ExecuteReader( command, &result );
	}

